import pandas as pd
from sqlalchemy import create_engine
import datetime
import openpyxl

engine = create_engine('mysql+pymysql://jsbi:jsbi-1701@47.114.55.19:9011/biv1?charset=utf8')
con = engine.connect()

# 增加自然周，后续会有按照自然周的分析
sqlLoop = " select 发货时间 from 财务_拼多多周度订单明细 where 自然周 is null "
dfLoop = pd.read_sql(sql=sqlLoop, con=engine)
for index, row in dfLoop.iterrows():
    # 按照发货时间，补发货时间的第几周
    if row['发货时间'] is not None:
        engine.execute(
            " update 财务_拼多多周度订单明细 set 自然周='" + str(
                datetime.datetime.strptime(row['发货时间'], '%Y-%m-%d %H:%M:%S').isocalendar()[1]) + "' where 发货时间='" + row[
                '发货时间'] + "' and 自然周 is null ")
        print("更新了一个产品的品类")
